const { getConnection } = require('../config/db');

class UserOperateModel {
  // 收藏视频
  static async collectVideo(openid, videoId) {
    const connection = await getConnection();
    try {
      // 检查是否已收藏
      const checkSql = 'SELECT id FROM user_operate WHERE openid = ? AND video_id = ? AND type = \'收藏\'';
      const [checkResult] = await connection.execute(checkSql, [openid, videoId]);
      
      if (checkResult.length > 0) {
        // 已收藏，更新时间
        const updateSql = 'UPDATE user_operate SET updated_at = NOW() WHERE openid = ? AND video_id = ? AND type = \'收藏\'';
        await connection.execute(updateSql, [openid, videoId]);
        return { inserted: false };
      } else {
        // 未收藏，添加收藏
        const insertSql = 'INSERT INTO user_operate (openid, video_id, type) VALUES (?, ?, \'收藏\')';
        const [result] = await connection.execute(insertSql, [openid, videoId]);
        return { inserted: true, result };
      }
    } finally {
      connection.release();
    }
  }

  // 取消收藏视频
  static async uncollectVideo(openid, videoId) {
    const connection = await getConnection();
    try {
      const sql = 'DELETE FROM user_operate WHERE openid = ? AND video_id = ? AND type = "收藏"';
      const [result] = await connection.execute(sql, [openid, videoId]);
      return result;
    } finally {
      connection.release();
    }
  }

  // 获取用户收藏列表
  static async getCollectionList(openid, page = 1, pageSize = 20) {
    const connection = await getConnection();
    try {
      // 强制数值化并兜底，避免 LIMIT 绑定 NaN 触发 ER_WRONG_ARGUMENTS
      const safePage = Number.isFinite(Number(page)) && Number(page) > 0 ? Number(page) : 1;
      const safePageSize = Number.isFinite(Number(pageSize)) && Number(pageSize) > 0 ? Number(pageSize) : 20;
      const offset = (safePage - 1) * safePageSize;
      
      const sql = `
        SELECT uo.*, v.title, v.cover_url, v.video_url, v.play_count
        FROM user_operate uo
        LEFT JOIN videos v ON uo.video_id = v.id
        WHERE uo.openid = ${connection.escape(openid)} AND uo.type = '收藏'
        ORDER BY uo.created_at DESC
        LIMIT ${offset}, ${safePageSize}
      `;
      
      const [rows] = await connection.query(sql);
      
      const countSql = 'SELECT COUNT(*) as total FROM user_operate WHERE openid = ? AND type = \'收藏\'';
      const [countResult] = await connection.execute(countSql, [openid]);
      
      return {
        list: rows,
        total: countResult[0].total,
        page: parseInt(String(safePage)),
        pageSize: parseInt(String(safePageSize))
      };
    } finally {
      connection.release();
    }
  }

  // 记录观看历史
  static async recordHistory(openid, videoId, progress = 0) {
    const connection = await getConnection();
    try {
      // 检查是否已有历史记录
      const checkSql = 'SELECT id FROM user_operate WHERE openid = ? AND video_id = ? AND type = \'历史\'';
      const [checkResult] = await connection.execute(checkSql, [openid, videoId]);
      
      if (checkResult.length > 0) {
        // 更新历史记录
        const updateSql = 'UPDATE user_operate SET progress = ?, updated_at = NOW() WHERE openid = ? AND video_id = ? AND type = \'历史\'';
        await connection.execute(updateSql, [progress, openid, videoId]);
      } else {
        // 添加历史记录
        const insertSql = 'INSERT INTO user_operate (openid, video_id, type, progress) VALUES (?, ?, \'历史\', ?)';
        await connection.execute(insertSql, [openid, videoId, progress]);
      }
      
      // 清理多余的历史记录（只保留最近100条）
      const cleanSql = `
        DELETE uo1 FROM user_operate uo1
        LEFT JOIN (
          SELECT id FROM user_operate 
          WHERE openid = ? AND type = '历史' 
          ORDER BY created_at DESC 
          LIMIT 100
        ) uo2 ON uo1.id = uo2.id
        WHERE uo1.openid = ? AND uo1.type = '历史' AND uo2.id IS NULL
      `;
      await connection.execute(cleanSql, [openid, openid]);
      
      return { success: true };
    } finally {
      connection.release();
    }
  }

  // 获取观看历史列表
  static async getHistoryList(openid, page = 1, pageSize = 20) {
    const connection = await getConnection();
    try {
      // 强制数值化并兜底，避免 LIMIT 绑定 NaN 触发 ER_WRONG_ARGUMENTS
      const safePage = Number.isFinite(Number(page)) && Number(page) > 0 ? Number(page) : 1;
      const safePageSize = Number.isFinite(Number(pageSize)) && Number(pageSize) > 0 ? Number(pageSize) : 20;
      const offset = (safePage - 1) * safePageSize;
      
      const sql = `
        SELECT uo.*, v.title, v.cover_url, v.video_url, v.play_count
        FROM user_operate uo
        LEFT JOIN videos v ON uo.video_id = v.id
        WHERE uo.openid = ${connection.escape(openid)} AND uo.type = '历史'
        ORDER BY uo.updated_at DESC
        LIMIT ${offset}, ${safePageSize}
      `;
      
      const [rows] = await connection.query(sql);
      
      const countSql = 'SELECT COUNT(*) as total FROM user_operate WHERE openid = ? AND type = \'历史\'';
      const [countResult] = await connection.execute(countSql, [openid]);
      
      return {
        list: rows,
        total: countResult[0].total,
        page: parseInt(String(safePage)),
        pageSize: parseInt(String(safePageSize))
      };
    } finally {
      connection.release();
    }
  }

  // 获取单个视频的观看进度
  static async getVideoProgress(openid, videoId) {
    const connection = await getConnection();
    try {
      const sql = 'SELECT progress FROM user_operate WHERE openid = ? AND video_id = ? AND type = \'历史\'';
      const [rows] = await connection.execute(sql, [openid, videoId]);
      
      if (rows.length > 0) {
        return {
          progress: rows[0].progress || 0
        };
      }
      
      return {
        progress: 0
      };
    } finally {
      connection.release();
    }
  }

  // 检查视频是否已收藏
  static async isCollected(openid, videoId) {
    const connection = await getConnection();
    try {
      const sql = 'SELECT id FROM user_operate WHERE openid = ? AND video_id = ? AND type = \'收藏\'';
      const [rows] = await connection.execute(sql, [openid, videoId]);
      return rows.length > 0;
    } finally {
      connection.release();
    }
  }
}

module.exports = UserOperateModel;